﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Linq;
using NPOI.Util;
using System.Reflection;

namespace Cyss.Core.Helper
{

    /// <summary>
    /// 
    /// </summary>
    public class ExcelHelper
    {

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="SheetNames">SheetName集合</param>
        /// <returns></returns>

        public static Stream CreateXLSWork<T>(IEnumerable<IEnumerable<T>> list, IEnumerable<string> SheetNames = null)
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();

            XSSFWorkbook workbook = new XSSFWorkbook();
            int index = 1;
            foreach (var items in list)
            {
                string sheetName = $"sheet{index}";
                if (SheetNames != null && SheetNames.Count() > index)
                {
                    sheetName = SheetNames.ElementAt(index - 1);
                }
                CreateSheet(workbook, items, sheetName);
                index++;
            }
            workbook.Write(bos);
            byte[] brray = bos.ToArray();
            InputStream Streams = new ByteArrayInputStream(brray);
            return Streams;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>

        public static Stream CreateXLSWork<T>(IEnumerable<T> list)
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();

            XSSFWorkbook workbook = new XSSFWorkbook();//一個sheet最多65536行

            for (int i = 0; i <= (list.Count() / 65534); i++)//每个Excel文件的一个页签只能存放65536行数据
            {
                CreateSheet(workbook, list.Skip(i * 65534).Take(65534), $"sheet{i}");
            }
            workbook.Write(bos);

            byte[] brray = bos.ToArray();
            InputStream Streams = new ByteArrayInputStream(brray);
            return Streams;
        }

        private static void CreateSheet<T>(XSSFWorkbook workbook, IEnumerable<T> list, string sheetName)
        {
            var row_index = 0;
            //创建Sheet
            workbook.CreateSheet(sheetName);
            //根据Sheet名字获得Sheet对象  
            var sheet = workbook.GetSheet(sheetName);
            IRow row;
            row = sheet.CreateRow(row_index);

            //写入标题
            int colIndex = 0;
            var pros = typeof(T).GetProperties();
            List<PropertyInfo> PropertyInfos = new List<PropertyInfo>();
            foreach (var pro in pros)
            {
                var proName = pro.Name;

                if (pro.PropertyType == typeof(CustomColumns) && list.Count() > 0)
                {
                    var customColumns = pro.GetValue(list.FirstOrDefault()) as CustomColumns;
                    foreach (var column in customColumns.Items)
                    {
                        if (column.Width > 0)
                        {
                            sheet.SetColumnWidth(colIndex, column.Width * 267);
                        }
                        row.CreateCell(colIndex++).SetCellValue(column.ColName);
                    }
                    PropertyInfos.Add(pro);
                }
                else
                {
                    var csvPropertie = pro.GetCustomAttributes(typeof(ExcelAttribute), true).FirstOrDefault() as ExcelAttribute;
                    if (csvPropertie == null)
                    {
                        continue;
                    }
                    proName = csvPropertie.ColName;
                    if (csvPropertie.Width > 0)
                    {
                        sheet.SetColumnWidth(colIndex, csvPropertie.Width * 267);
                    }
                    row.CreateCell(colIndex++).SetCellValue(proName);

                    PropertyInfos.Add(pro);
                }
            }
            row = sheet.CreateRow(++row_index);


            foreach (var item in list)
            {
                colIndex = 0;
                foreach (var pro in PropertyInfos)
                {

                    if (pro.PropertyType == typeof(CustomColumns) && list.Count() > 0)
                    {
                        var customColumns = pro.GetValue(item) as CustomColumns;
                        if (customColumns == null)
                        {
                            colIndex++;
                            continue;
                        }
                        foreach (var column in customColumns.Items)
                        {
                            if (column.Value == null || string.IsNullOrWhiteSpace(column.Value.ToString()))
                            {
                                colIndex++;
                                continue;
                            }
                            if (column.Width > 0)
                            {
                                sheet.SetColumnWidth(colIndex, column.Width * 267);
                            }
                            string cellValue = column.Value.ToString();
                            if (column.Type == CellValueType.Int && cellValue.IsDouble())
                            {
                                row.CreateCell(colIndex++).SetCellValue(Convert.ToDouble(column.Value));
                            }
                            else
                            {
                                row.CreateCell(colIndex++).SetCellValue(column.Value.ToString());
                            }

                        }
                    }
                    else
                    {

                        var value = pro.GetValue(item);
                        if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
                        {
                            colIndex++;
                            continue;
                        }

                        var csvPropertie = pro.GetCustomAttributes(typeof(ExcelAttribute), true).FirstOrDefault() as ExcelAttribute;
                        string cellValue = value.ToString();

                        if (csvPropertie != null && !string.IsNullOrWhiteSpace(csvPropertie.FormatString))
                        {
                            if (value.GetType() == typeof(DateTime) || value.GetType() == typeof(DateTime?))
                            {
                                cellValue = Convert.ToDateTime(value).ToString(csvPropertie.FormatString);
                            }
                        }
                        if (csvPropertie.Type == CellValueType.Int && cellValue.IsDouble())
                        {
                            row.CreateCell(colIndex++).SetCellValue(Convert.ToDouble(cellValue));
                        }
                        else
                        {
                            row.CreateCell(colIndex++).SetCellValue(cellValue);
                        }

                    }
                }
                row = sheet.CreateRow(++row_index);
            }
        }

        /// <summary>
        /// 更新excel
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="excelType"></param>
        /// <param name="eidtCells"></param>

        public static byte[] UpdateExcel(Stream stream, ExcelType excelType, IEnumerable<ExcelEidtCell> eidtCells)
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();

            IWorkbook wk = null;
            if (excelType == ExcelType.XLS)
            {
                wk = new HSSFWorkbook(stream);
            }
            else
            {
                wk = new XSSFWorkbook(stream);
            }
            ISheet sheet = wk.GetSheetAt(0);

            foreach (var rowGroup in eidtCells.GroupBy(x => x.RowIndex).OrderBy(x => x.Key))
            {
                var row = sheet.GetRow(rowGroup.Key);
                foreach (var cellItem in rowGroup)
                {
                    ICell cell = null;
                    try
                    {
                        cell = row.GetCell(cellItem.CellIndex);
                        if (cell == null)
                        {
                            cell = row.CreateCell(cellItem.CellIndex);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception($"行{rowGroup.Key}列{cellItem.CellIndex}不存在");
                    }

                    if (cellItem.Value == null || string.IsNullOrWhiteSpace(cellItem.Value.ToString()))
                    {
                        cell.SetCellValue(string.Empty);
                        continue;
                    }
                    string cellValue = cellItem.Value.ToString();

                    if (!string.IsNullOrWhiteSpace(cellItem.FormatString))
                    {
                        if (cellItem.Value.GetType() == typeof(DateTime) || cellItem.Value.GetType() == typeof(DateTime?))
                        {
                            cellValue = Convert.ToDateTime(cellItem.Value).ToString(cellItem.FormatString);
                        }
                    }
                    if (cellItem.ValueType == CellValueType.Int && cellValue.IsDouble())
                    {
                        cell.SetCellValue(Convert.ToDouble(cellValue));
                    }
                    if (cellItem.ValueType == CellValueType.DateTime)
                    {
                        cell.SetCellValue(Convert.ToDateTime(cellValue));
                    }
                    if (cellItem.ValueType == CellValueType.String)
                    {
                        cell.SetCellValue(cellValue);
                    }
                }
            }
            wk.Write(bos,false);
            wk.Close();
            return bos.ToByteArray();
        }

        ///<summary>
        ///
        /// 
        ///<summary>
        /// #region 两种不同版本的操作excel
        /// 扩展名*.xlsx
        /// </summary>
        public static DataTable GetDataTable(Stream stream, ExcelType excelType)
        {

            DataTable dt = new DataTable();
            ISheet Sheet;
            int RemoveCount = 0;
            if (excelType == ExcelType.XLS)
            {
                //创建一个webbook，对应一个Excel文件(用于xls文件导入类)
                HSSFWorkbook Hssfworkbook = new HSSFWorkbook(stream);
                //RemoveCount = GetINPOCount(Hssfworkbook);
                Sheet = Hssfworkbook.GetSheetAt(0);
            }
            else
            { //扩展名 *.xlsx
                XSSFWorkbook Hssfworkbook = new XSSFWorkbook(stream);
                //RemoveCount = GetINPOCount(Hssfworkbook);
                Sheet = Hssfworkbook.GetSheetAt(0);
            }

            IEnumerator rows = Sheet.GetRowEnumerator();
            for (int j = 0; j < (Sheet.GetRow(RemoveCount).LastCellNum); j++)
            {
                try
                {
                    var cell = Sheet.GetRow(RemoveCount).Cells[j];
                    dt.Columns.Add(GetCellValue(cell));
                }
                catch (Exception ex)
                {
                    dt.Columns.Add($"Column{j}");
                }
            }
            try
            {
                while (rows.MoveNext())
                {
                    XSSFRow row = (XSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        NPOI.SS.UserModel.ICell Cell = row.GetCell(i);
                        if (dr.ItemArray.Length <= i)
                        {
                            break;
                        }
                        if (Cell == null)//当表格中的值为空字符串时进行判断
                        {
                            dr[i] = "";
                        }
                        else
                        {
                            //注意**** 这里需要进行时间类型类型转换
                            if (Cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(Cell))
                            {//这里要做时间处理
                                try
                                {
                                    dr[i] = Cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                catch (Exception ex)
                                {
                                    dr[i] = "";
                                }
                            }
                            else
                            {
                                dr[i] = GetCellValue(Cell);
                            }
                        }
                    }
                    if (!string.IsNullOrWhiteSpace(string.Join("", dr.ItemArray)))
                    {
                        dt.Rows.Add(dr);
                    }

                }
            }
            catch (Exception ex)
            {
                dt.Rows.RemoveAt(0);
                return null;
            }
            if (RemoveCount > 0)
            {
                for (int i = 0; i <= RemoveCount; i++)
                {
                    dt.Rows.RemoveAt(0);
                }
            }
            else
            {
                dt.Rows.RemoveAt(0);
            }
            return dt;
        }


        ///<summary>
        ///
        /// 
        ///<summary>
        /// #region 两种不同版本的操作excel
        /// 扩展名*.xlsx
        /// </summary>
        public static IEnumerable<T> GetList<T>(Stream stream, ExcelType excelType) where T : new()
        {
            var dataTable = GetDataTable(stream, excelType);
            return dataTable.GetList<T>();
        }


        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator Hss = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        Hss.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        cell.SetCellType(CellType.String);
                        return cell.StringCellValue;
                    }
            }
        }
    }

    public enum ExcelType
    {
        XLS = 1,
        XLSX = 2

    }
}
